using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class PersonDAO:DBAccess
    {
        public PersonDAO() { }
        #region sql command
        string SQL_SELECT_ID = "select max(PersonId) from Person";
        string SQL_SELECT_ALL = "SELECT Person.PersonID, Person.Name, Person.Gender, Person.DateOfBirth,  Person.Note from Person";
        string SQL_INSERT = "insert into Person([Name],Gender,DateOfBirth,[Note]) values(@name,@gender,@dateOfBirth,@note)";
        string SQL_UPDATE = "update [Person] set [Name]=@name,[Gender]=@gender,[DateOfBirth]=@dateOfBirth,[Note]=@note where PersonID=@personID";
        string SQL_DELETE = "delete from Person where PersonID=@personID";
        #endregion
        #region fields
        string message;
        ContactDAO daoContact = new ContactDAO();
        #endregion
        #region properties
        public string Message { get { return message; } }

        #endregion
        /// <summary>
        /// Get all person
        /// </summary>
        /// <returns></returns>
        public IList<Person> SelectAll()
        {
            IList<Person> list = new List<Person>();
            OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL,conn);
            if (Connect())
            {
                OleDbDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Person person = new Person();
                    person.PersonID = dr.GetInt32(0);
                    person.Name = dr.GetString(1);
                    person.Gender = dr.GetBoolean(2);
                    person.DateOfBirth = dr.GetDateTime(3);   
                    person.Note = dr.GetString(4);
                    person.Contact = daoContact.SelectByPersonId(person.PersonID);                    
                    list.Add(person);
                }
                Disconnect();
            }
            return list;
        }
        public int Insert(Person person)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@name", OleDbType.VarChar).Value=person.Name;
                command.Parameters.Add("@gender", OleDbType.Boolean).Value=person.Gender;
                command.Parameters.Add("@dateOfBirth", OleDbType.Date).Value=person.DateOfBirth;
                command.Parameters.Add("@note", OleDbType.VarChar).Value=person.Note;
               
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                   
                    command.CommandText = SQL_SELECT_ID;                    
                    person.PersonID = int.Parse(command.ExecuteScalar().ToString());
                    if (effectedRows > 0) daoContact.Insert(person.Contact, person);
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Update(Person person)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
                command.Parameters.AddWithValue("@name", OleDbType.VarChar).Value = person.Name;
                command.Parameters.AddWithValue("@gender", OleDbType.Boolean).Value = person.Gender;
                command.Parameters.AddWithValue("@dateOfBirth", OleDbType.Date).Value = person.DateOfBirth;
                command.Parameters.AddWithValue("@note", OleDbType.VarChar).Value = person.Note;
                command.Parameters.AddWithValue("@personID", OleDbType.Integer).Value = person.PersonID;
                if (Connect())
                {
                    //if(daoEmail.Update(person.Contact.Emails
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Delete(Person person)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
                command.Parameters.AddWithValue("@personID", OleDbType.Integer).Value = person.PersonID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
    }
}
